package com.sifei.seed.model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component // Spring框架扫描本注解会将类的对象实例化并管理
public class AppMusicSongDao {
    @Autowired // 通过Spring框架自动注入对象
    private JdbcTemplate jdbcTemplate;

    public List<AppMusicSong> getSongList(Integer pageSize, Integer pageNum) {
        final String sql = "select songid,tb_song.singerid,songName,fileUrl,lyric,singerName,type,songlink from tb_song left join tb_artist on tb_song.singerid=tb_artist.singerid";
        List<AppMusicSong> musicSongList = jdbcTemplate.query(sql, new AppMusicSongMapper());
        return musicSongList;
    }

    public AppMusicSong getSongBysongid(final Integer songid) {
        final String sql = "select songid,tb_song.singerid,songName,fileUrl,lyric,singerName,type,songlink from tb_song left join tb_artist on tb_song.singerid=tb_artist.singerid where songid=" + songid + "";
        List<AppMusicSong> musicConmunitiesList = jdbcTemplate.query(sql, new AppMusicSongMapper());
        if (musicConmunitiesList == null || musicConmunitiesList.isEmpty()) {
            return null;
        }
        return musicConmunitiesList.get(0);
    }

    public AppMusicSong getSongBysongName(final Long songName) {
        final String sql = "select songid,tb_song.singerid,songName,fileUrl,lyric,singerName,type from tb_song left join tb_artist on tb_song.singerid=tb_artist.singerid where songName='" + songName + "' limit 1";
        List<AppMusicSong> musicConmunitiesList = jdbcTemplate.query(sql, new AppMusicSongMapper());
        if (musicConmunitiesList == null || musicConmunitiesList.isEmpty()) {
            return null;
        }
        return musicConmunitiesList.get(0);
    }

    class AppMusicSongMapper implements RowMapper<AppMusicSong> {
        @Override
        public AppMusicSong mapRow(ResultSet rs, int rowNum) throws SQLException {
            AppMusicSong musicSong = new AppMusicSong();
            musicSong.setSongid(rs.getInt("songid"));
            musicSong.setSingerid(rs.getInt("singerid"));
            musicSong.setSongName(rs.getString("songName"));
            musicSong.setFileUrl(rs.getString("fileUrl"));
            musicSong.setLyric(rs.getString("lyric"));
            musicSong.setSingerName(rs.getString("singerName"));
            musicSong.setType(rs.getString("type"));
            musicSong.setSonglink(rs.getString("songlink"));
            return musicSong;
        }
    }
}
